在 ClickHouse 的查詢加速機制中,除了 Partition Pruning 進行粗篩外,另一個細緻化資料範圍掃描的關鍵機制就是 Primary Key (主鍵索引)、Sorting Key (排序鍵) 與 Granule 索引 (粒度索引)。
在 ClickHouse 中,Primary Key 與傳統 OLTP 資料庫中的「唯一鍵 (Unique Constraint)」不同,它 不保證資料唯一性,也不會自動加索引樹 (如 B-Tree)。
ClickHouse 的 Primary Key 是用來 決定資料在磁碟中的物理排序方式 (Clustered Index),它是 MergeTree 引擎搜尋資料的首要索引依據。
CREATE TABLE orders
(
order_date Date,
user_id UInt64,
order_id UInt64,
amount Float64
) ENGINE = MergeTree
PARTITION BY toYYYYMM(order_date)
ORDER BY (user_id, order_date);
這裡的 Primary Key 是 (user_id, order_date)
,資料會依此排序寫入磁碟。
名稱 | 說明 |
---|---|
Primary Key | 排序索引 (Clustered Index),實際儲存時資料排序依據 |
Sorting Key | 與 Primary Key 同義,但更偏向強調排序邏輯層級的用詞 |
Granule 是 ClickHouse 將資料拆分為查詢時可裁剪最小單位的「資料區塊」。
一個 Granule 會包含數千筆資料 (預設為 8192 rows),系統會為每個 Granule 儲存該範圍內的 Sorting Key 最小值與最大值 (min-max 索引)。
SELECT * FROM orders WHERE user_id = 123456 AND order_date >= '2025-08-01';
user_id = 123455 ~ 123455
→ 跳過user_id = 123456 ~ 123456
→ 讀取user_id = 123457 ~ 123458
→ 跳過這種裁剪動作是查詢能夠在 TB 級資料中僅掃描少量資料的關鍵。
比較項目 | Primary Key (範圍索引) | Secondary Index (Data Skipping Index) |
---|---|---|
運作方式 | 資料寫入時排序,查詢時透過 Granule 索引範圍裁剪 | 查詢時依欄位值範圍 (min-max / bloom filter) 決定是否讀取 |
查詢效率 | 查詢條件若符合排序欄位 → 裁剪效率極佳 | 可支援非排序欄位的查詢過濾,但效率不如 Primary Key |
建立方式 | 透過 ORDER BY 設定,與 MergeTree 強耦合 | 需額外建立 (ALTER TABLE ADD INDEX...) |
適用查詢 | 範圍查詢、序列查詢、依排序邏輯為主的查詢 | 高基數欄位查詢(如特定 tag、keyword) |
設計策略 | 適用場景 |
---|---|
常查詢範圍條件放最前面 | 例如 user_id、device_id 若常作為 WHERE 條件,應放排序鍵首位 |
從高選擇性到低選擇性排序 | user_id → event_date,讓 Granule 範圍更集中,裁剪更精準 |
避免將高變異但不查詢的欄位設為排序鍵 | 如 UUID、隨機 hash,排序無助於裁剪,只會造成合併成本上升 |
結合 Partition 與 Sorting Key 設計 | Partition 粗裁剪、Primary Key 精裁剪,讓查詢僅需掃描極小範圍資料 |
ClickHouse 的 Primary Key 並不是傳統資料庫的全索引(如 B-Tree),而是設計成「Sparse (稀疏) 索引」,它透過 Granule (粒度區塊) 來達到大規模資料快速篩選的效果。
每個 Granule 只記錄首筆資料的 Primary Key 值:例如預設 Granule 粒度為 8192 筆,索引只會紀錄每個 Granule 第一筆資料的主鍵值。
Sparse 索引非常精簡,能完全載入記憶體中,即使資料量達到數百億筆,索引仍僅需占用少量記憶體空間。
每個 MergeTree 的 Data Part 都有獨立 Primary Index,查詢時這些索引會分別比對以達到最佳裁剪效果。
查詢時,ClickHouse 根據 WHERE 條件與 Sparse Primary Index 比對 Granule 範圍:
ClickHouse 提供了幾個實用的指令來協助你查看索引運作狀況:
mergeTreeIndex
table functionSELECT * FROM mergeTreeIndex('your_database.your_table', 'primary_key') LIMIT 10;
這可以幫助你看到每個 Granule 第一筆資料的 Primary Key 值,了解索引結構。
EXPLAIN
確認索引是否被裁剪:EXPLAIN PLAN SELECT * FROM orders WHERE user_id = 123456;
system.parts
觀察查詢裁剪統計:SELECT partition, active, rows, bytes_on_disk
FROM system.parts
WHERE table = 'orders' AND active;
透過 index_granularity
設定:
CREATE TABLE t (...) ENGINE = MergeTree() ORDER BY ... SETTINGS index_granularity = 4096;
粒度越小,裁剪效率越高,但會增加索引大小與查詢時的 CPU 負擔。
粒度越大,索引資料少,CPU 開銷低,但裁剪不精確,I/O 負擔較大。
Best Practice | 說明 |
---|---|
Primary Key 欄位數量建議 1~3 欄位 | 過多欄位會增加排序成本與合併負擔,減少裁剪效果 |
裁剪效率依賴查詢條件與排序鍵的吻合度 | WHERE 條件若能對應到排序鍵首欄位,裁剪效果最佳 |
index_granularity 避免過度微調 | 除非有特殊需求,否則不建議大幅修改,預設 8192 通常是性能與資源平衡的最佳值 |
結合 Partition 設計分層裁剪查詢 | Partition 負責粗裁剪,Primary Key 精裁剪,能讓 TB 級資料也只需秒級查詢 |
可配合 Secondary Index 提升非排序欄位查詢效率 | 如需查詢非 Primary Key 欄位 (如 tags),可搭配 Bloom Filter Index 加速裁剪查詢 |
Primary Key 與 Granule 索引是 ClickHouse 能在海量資料中做到毫秒級查詢的核心技術。透過合理設計 Sorting Key、調整粒度、結合 Partition Pruning,能讓資料掃描量降到最小,大幅提升查詢性能。